package july.android.cakes.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.List;
import java.util.LinkedList;
import java.util.Map;
import java.util.HashMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author S_cake
 * @version 课表信息 Service
 */
@Service
@Transactional
public class STimetable {

    @Autowired
    private JdbcTemplate jdbc;

    private String saturday = "false";
    private String sunday = "false";
    private String eleven = "false";

    //获取学生课程表
    public List<Map<String, Object>> studentCourse(String stu_id) {
        List<Map<String, Object>> course_id;
        List list = new LinkedList();
        List<Map<String, Object>> temp = new LinkedList();
        Map map = new HashMap();

        String ma_id = jdbc.queryForMap("select ma_id from student where id=?", stu_id).values().toString();
        ma_id = ma_id.substring(1, ma_id.length() - 1);
        String grade = jdbc.queryForMap("select grade from student where id=?", stu_id).values().toString();
        grade = grade.substring(1, grade.length() - 1);
        course_id = jdbc.queryForList("select cou_id from cou2stu where stu_id=?", stu_id);

        for (Map<String, Object> item : course_id) {
            String cou_id = item.values().toString().subSequence(1, item.values().toString().length() - 1).toString();
            List<Map<String, Object>> course_time = jdbc.queryForList("select * from course_time where cou_id=? and ma_id=? and grade=? and isnull(special) ", cou_id, ma_id, grade);
            for (Map<String, Object> item2 : course_time) {
                map.putAll(stuTransform_null(item2));
            }
        }

        for (Map<String, Object> item : course_id) {
            String cou_id = item.values().toString().subSequence(1, item.values().toString().length() - 1).toString();
            List<Map<String, Object>> course_time = jdbc.queryForList("select * from course_time where cou_id=? and ma_id=? and grade=? and special<>'' ", cou_id, ma_id, grade);
            for (Map<String, Object> item2 : course_time) {
                temp.add(stuTransform(item2));
            }
        }
        temp = merge(temp);

        for (Map<String, Object> item : temp) {
            map.putAll(item);
        }

        map.put("saturday", saturday);
        map.put("sunday", sunday);
        map.put("eleven", eleven);
        list.add(map);

        saturday = "false";
        sunday = "false";
        eleven = "false";

        return list;
    }

    //获取教师课程表
    public List<Map<String, Object>> teacherCourse(String tea_id) {
        List<Map<String, Object>> grade_major;
        List<Map<String, Object>> course_id;
        List list = new LinkedList();
        List<Map<String, Object>> temp = new LinkedList();
        Map map = new HashMap();

        course_id = jdbc.queryForList("select cou_id from cou2tea where tea_id=?", tea_id);
        grade_major = jdbc.queryForList("select * from tea2ma where tea_id=?", tea_id);

        for (Map<String, Object> item : grade_major) {
            String ma_id = item.get("grade").toString();
            String grade = item.get("ma_id").toString();
            for (Map<String, Object> item2 : course_id) {
                String cou_id = item2.get("cou_id").toString();
                List<Map<String, Object>> course_time = jdbc.queryForList("select * from course_time where cou_id=? and isnull(special)", cou_id);
                for (Map<String, Object> item3 : course_time) {
                    String subMa_id = item3.get("grade").toString();
                    String subGrade = item3.get("ma_id").toString();
                    if (subMa_id.equals(ma_id) && subGrade.equals(grade)) {
                        map.putAll(teaTransform_null(item3));
                    }
                }

                List<Map<String, Object>> course_time2 = jdbc.queryForList("select * from course_time where cou_id=? and special<>''", cou_id);
                for (Map<String, Object> item3 : course_time2) {
                    String subMa_id = item3.get("grade").toString();
                    String subGrade = item3.get("ma_id").toString();
                    if (subMa_id.equals(ma_id) && subGrade.equals(grade)) {
                        temp.add(teaTransform(item3));
                    }
                }
            }
        }

        temp = merge(temp);

        for (Map<String, Object> item : temp) {
            map.putAll(item);
        }

        map.put("saturday", saturday);
        map.put("sunday", sunday);
        map.put("eleven", eleven);
        list.add(map);

        saturday = "false";
        sunday = "false";
        eleven = "false";

        return list;
    }

    //处理学生special为null的查询结果
    private Map<String, Object> stuTransform_null(Map<String, Object> orimap) {
        Map map = new HashMap();
        String left = orimap.get("ttime").toString();
        String right = null;
        String room = orimap.get("room").toString();
        String cou_id = orimap.get("cou_id").toString();
        String name = jdbc.queryForMap("select nname from course where id=?", cou_id).get("nname").toString();
        String tea_id = jdbc.queryForMap("select tea_id from cou2tea where cou_id=?", cou_id).get("tea_id").toString();
        String teacher = jdbc.queryForMap("select nname from teacher where id=?", tea_id).get("nname").toString();

        if (left.charAt(0) == '6') {
            saturday = "true";
        }
        if (left.charAt(0) == '7') {
            sunday = "true";
        }

        if (left.length() == 3 && left.substring(1, 3).equals("11")) {
            eleven = "true";

        }

        right = name + "<br />" + teacher + "<br />"
                + room;
        map.put(left, right);
        return map;
    }

    //处理教师special为null的查询结果
    private Map<String, Object> teaTransform_null(Map<String, Object> orimap) {
        Map map = new HashMap();
        String left = orimap.get("ttime").toString();
        String right = null;
        String room = orimap.get("room").toString();
        String cou_id = orimap.get("cou_id").toString();
        String name = jdbc.queryForMap("select nname from course where id=?", cou_id).get("nname").toString();
        String ma_id = orimap.get("ma_id").toString();
        String ma_name = jdbc.queryForMap("select nname from major where id=?", ma_id).get("nname").toString();
        String grade = orimap.get("grade").toString();

        if (left.charAt(0) == '6') {
            saturday = "true";
        }
        if (left.charAt(0) == '7') {
            sunday = "true";
        }

        if (left.length() == 3 && left.substring(1, 3).equals("11")) {
            eleven = "true";
        }

        right = name + "<br />" + grade + " " + ma_name + "<br />" + room;
        map.put(left, right);
        return map;
    }

    //处理学生special为非null的查询结果
    private Map<String, Object> stuTransform(Map<String, Object> orimap) {
        Map map = new HashMap();
        String special = orimap.get("special").toString();
        String left = orimap.get("ttime").toString();
        String right = null;
        String cou_id = orimap.get("cou_id").toString();
        String name = jdbc.queryForMap("select nname from course where id=?", cou_id).get("nname").toString();
        String tea_id = jdbc.queryForMap("select tea_id from cou2tea where cou_id=?", cou_id).get("tea_id").toString();
        String teacher = jdbc.queryForMap("select nname from teacher where id=?", tea_id).get("nname").toString();
        String room = null;

        if (left.charAt(0) == '6') {
            saturday = "true";
        }
        if (left.charAt(0) == '7') {
            sunday = "true";
        }

        if (left.length() == 3 && left.substring(1, 3).equals("11")) {
            eleven = "true";
        }

        try {
            room = orimap.get("room").toString();
        } catch (Exception e) {
        }
        switch (special) {
            case "场所不定":
                right = name + "<br />" + teacher;
                break;
            case "单周":
                right = special + "<br />" + name + "<br />" + teacher + "<br />" + room + "<br />";
                break;
            case "双周":
                right = special + "<br />" + name + "<br />" + teacher + "<br />" + room;
                break;
            default:
        }

        map.put(left, right);
        return map;
    }

    //处理教师special为非null的查询结果
    private Map<String, Object> teaTransform(Map<String, Object> orimap) {
        Map map = new HashMap();
        String special = orimap.get("special").toString();
        String left = orimap.get("ttime").toString();
        String right = null;
        String cou_id = orimap.get("cou_id").toString();
        String name = jdbc.queryForMap("select nname from course where id=?", cou_id).get("nname").toString();
        String ma_id = orimap.get("ma_id").toString();
        String ma_name = jdbc.queryForMap("select nname from major where id=?", ma_id).get("nname").toString();
        String grade = orimap.get("grade").toString();
        String room = null;
        try {
            room = orimap.get("room").toString();
        } catch (Exception e) {
        }

        if (left.charAt(0) == '6') {
            saturday = "true";
        }
        if (left.charAt(0) == '7') {
            sunday = "true";
        }

        if (left.length() == 3 && left.substring(1, 3).equals("11")) {
            eleven = "true";
        }

        switch (special) {
            case "场所不定":
                right = name + "<br />" + grade + " " + ma_name;
                break;
            case "单周":
                right = special + "<br />" + name + "<br />" + grade + " " + ma_name + "<br />" + room + "<br />";
                break;
            case "双周":
                right = special + "<br />" + name + "<br />" + grade + " " + ma_name + "<br />" + room;
                break;
            default:
        }

        map.put(left, right);
        return map;
    }

    //合并单双周课程
    private List<Map<String, Object>> merge(List<Map<String, Object>> orimap) {
        List list = new LinkedList();
        String left = null;
        String right = null;
        String value = null;
        String key = null;
        for (Map<String, Object> item3 : orimap) {
            Map map = new HashMap();
            value = item3.values().toString();
            value = value.substring(1, 3);
            key = item3.keySet().toString();
            key = key.substring(1, key.length() - 1);
            switch (value) {
                case "体育":
                    list.add(item3);
                    break;
                case "单周":
                    left = key;
                    String subKey = null;
                    String subValue = null;
                    for (int i = 0; i < orimap.size(); i++) {
                        subKey = orimap.get(i).keySet().toString();
                        subKey = subKey.substring(1, subKey.length() - 1);
                        subValue = orimap.get(i).values().toString();
                        subValue = subValue.substring(1, 3);
                        if (key.equals(subKey) && subValue.equals("双周")) {
                            right = item3.values().toString().substring(1, item3.values().toString().length() - 1) + orimap.get(i).values().toString().substring(1, orimap.get(i).values().toString().length() - 1);
                            break;
                        }
                    }
                    map.put(left, right);
                    map.put("9" + left, right.indexOf("双周"));
                    list.add(map);
                    break;
                default:
            }
        }

        return list;
    }

}
